var express = require('express');
var mysql = require('mysql');
var utils = require('../utils');
var router = express.Router();

var database = require('../database.config');
var connection = mysql.createConnection(database);
connection.connect();

router.post('/list', function (req, res, next) {
	const params = req.body;

	if (!params.fromChatCode) {
		res.send(utils.sendData(null, 500, '缺少字段！'))
		return;
	}

	var sql = `SELECT
	*
FROM
	(
		SELECT
			a.id,
			a.chatCode,
			a.notHint,
			a.user_chatCode,
			b.telephone,
			b.userName,
			b.sex,
			b.headImg,
			b.userProfile,
			b.province,
			b.city
		FROM
			chat_info a
		LEFT JOIN chat_user b ON a.chatCode = b.chatCode
		WHERE
			a.user_chatCode = '${params.fromChatCode}'
	) f
LEFT JOIN (
	SELECT
		e.maxTime,
		e.userToChatCode,
		f.type,
		f.content,
		f.time,
		f.user_to_chatCode
	FROM
		(
			SELECT
				MAX(d.time) AS maxTime,
				d.user_to_chatCode userToChatCode
			FROM
				chat_info i
			LEFT JOIN chat_info_detail d ON i.chatCode = d.user_to_chatCode
			AND i.user_chatCode = d.user_from_chatCode
			WHERE
				i.user_chatCode = '${params.fromChatCode}'
			GROUP BY
				userToChatCode
		) e
	LEFT JOIN chat_info_detail f ON e.userToChatCode = f.user_to_chatCode
	AND e.maxTime = f.time
) c ON f.chatCode = c.user_to_chatCode`
	connection.query(sql, function (error, result) {
		if (error) throw error;
		if (result.length > 0) {
			result.forEach(element => {
				element.base = 'http://' + req.host + ':' + global.port;
			});
		}
		res.send(utils.sendData(result));
	});
});

router.post('/detail', function (req, res, next) {
	const params = req.body;
	if (!params.fromChatCode || !params.toChatCode) {
		res.send(utils.sendData(null, 500, '缺少字段！'))
		return;
	}

	var sql = `SELECT
	info2.*, u2.chatCode toChatCode,
	u2.telephone toTelephone,
	u2.userName toUserName,
	u2.sex toSex,
	u2.headImg toHeadImg,
	u2.userProfile toUserProfile,
	u2.province toProvince,
	u2.city toCity
FROM
	(
		SELECT
			info1.id,
			info1.type,
			info1.content,
			info1.time,
			info1.user_to_chatCode,
			u1.chatCode fromChatCode,
			u1.telephone fromTelephone,
			u1.userName fromUserName,
			u1.sex fromSex,
			u1.headImg fromHeadImg,
			u1.userProfile fromUserProfile,
			u1.province fromProvince,
			u1.city fromCity
		FROM
			(
				SELECT
					*
				FROM
					chat_info_detail d
				WHERE
					d.user_from_chatCode = '${params.fromChatCode}'
				AND d.user_to_chatCode = '${params.toChatCode}'
			) info1
		LEFT JOIN chat_user u1 ON info1.user_from_chatCode = u1.chatCode
	) info2
LEFT JOIN chat_user u2 ON info2.user_to_chatCode = u2.chatCode`

	connection.query(sql, function (error, result) {
		if (error) throw error;
		if (result.length > 0) {
			result.forEach(element => {
				element.base = 'http://' + req.host + ':' + global.port;
			});
		}
		res.send(utils.sendData(result));
	});
});

router.post('/add', function (req, res, next) {
	const params = req.body;

	if (!params.toChatCode || !params.fromChatCode) {
		res.send(utils.sendData('缺少标识！', 500));
		return;
	}
	var selSql = `SELECT * from chat_info i where i.chatCode='${params.toChatCode}' AND i.user_chatCode='${params.fromChatCode}'`;
	// 查询是否已存在插入的该条数据
	connection.query(selSql, function (error, result) {
		if (error) throw error;
		// 如果不存在则插入
		if (result.length <= 0) {
			var addSql = `INSERT INTO chat_info (chatCode, notHint, user_chatCode) VALUES (?, ?, ?)`;
			var addSqlParams = [params.toChatCode, null, params.fromChatCode];
			connection.query(addSql, addSqlParams, function (err, results) {
				if (err) throw err;

				// 查询出插入的该条数据
				var sqlSelTwo = `SELECT
					a.id,
					a.chatCode,
					a.notHint,
					a.user_chatCode,
					b.telephone,
					b.userName,
					b.sex,
					b.headImg,
					b.userProfile,
					b.province,
					b.city
				FROM
					chat_info a
				LEFT JOIN chat_user b ON a.chatCode = b.chatCode
				WHERE
				a.user_chatCode = '${params.fromChatCode}' AND a.chatCode='${params.toChatCode}'`
				connection.query(sqlSelTwo, function (e, r) {
					if (e) throw r;
					if (r.length > 0) {
						r.forEach(element => {
							element.base = 'http://' + req.host + ':' + global.port;
						});
					}
					res.send(utils.sendData(r[0]))
				})
			});
		} else {
			res.send(utils.sendData(true));
		}
	})
})

router.post('/delete', function (req, res, next) {
	const params = req.body;
	if (!params.chatCode || !params.userChatCode) {
		res.send(utils.sendData('参数格式正确', 500));
	}

	var delChatInfo = `DELETE FROM chat_info WHERE chatCode='${params.chatCode}' AND user_chatCode='${params.userChatCode}'`
	var delChatInfoDetail = `DELETE FROM chat_info_detail WHERE user_from_chatCode='${params.userChatCode}' AND user_to_chatCode='${params.chatCode}'`

	connection.query(delChatInfo, function (error, result) {
		if (error) throw error;
		connection.query(delChatInfoDetail, function (e, r) {
			if (e) throw e;
			res.send(utils.sendData('操作成功'));
		})
	})
})

router.post('/add/detail', function (req, res, next) {
	const params = req.body;

	var addSql = `INSERT INTO chat_info_detail (type, content, time, user_from_chatCode, user_to_chatCode) VALUES (?, ?, ?, ?, ?)`;
	var addSqlParams = [params.type, params.content, new Date().getTime(), params.fromChatCode, params.toChatCode]

	connection.query(addSql, addSqlParams, function (error, result) {
		if (error) throw error;
		res.send(utils.sendData(result));
	});

	// if (params.length > 0) {
	//   for (let index = 0; index < params.length; index++) {
	//     const element = params[index];
	//     var arr = [element.type, element.content, element.time, element.fromChatCode, element.toChatCode];
	//     addSqlParams.push(arr);
	//   }

	//   connection.query(addSql, [addSqlParams], function (error, result) {
	//     if (error) throw error;
	//     res.send(utils.sendData(result));
	//   });
	// }
})

module.exports = router;